/*
	Persister Class
*/
package com.soshalboard.persistence;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.jdbc.support.rowset.SqlRowSet;

import com.soshalboard.config.DbToView;
import com.soshalboard.persistence.domain.Category;
import com.soshalboard.persistence.domain.Product;
import com.soshalboard.persistence.domain.Session;
import com.soshalboard.persistence.domain.User;
import com.soshalboard.persistence.domain.WishList;

/**
 * Spring's helper for working with JDBC.
 */
public class DBPersister {
	private JdbcTemplate jdbcTemplate;
	private static Log log = LogFactory.getLog(DBPersister.class);
	private DbToView dbToView;

	public DBPersister(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public void saveSession(Session session) {
		String query = "INSERT INTO session (id, user_id, created_time) "
				+ "VALUES ('%s', '%s', NOW())";

		final String queryString = String.format(query, session.getSessionId(),
				session.getUserId());

		this.jdbcTemplate.update(queryString);
	}
	
	public Session getSession(Integer userId) {

		String query = "SELECT id from session where user_id = '%s'";
		final String queryString = String.format(query, userId);

		Session session = null;
		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryString);

			while (rowSet.next()) {
					session = new Session();
					session.setSessionId(rowSet.getString("id"));
					session.setUserId(userId);
			}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryString);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryString);
		}
		
		return session;
	}
	
	
	public Integer getUserByFBUserName(String fbUserName) {

		String query = "SELECT id from user where fb_user_name = '%s'";
		final String queryString = String.format(query, fbUserName);

		Integer userId = null;
		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryString);

			while (rowSet.next()) {
				userId = rowSet.getInt("id");
			}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryString);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryString);
		}
		
		return userId;
	}
	
	
	public Integer getUserByFBId(Integer fbUserId) {

		String query = "SELECT id from user where fb_user_id = '%s'";
		final String queryString = String.format(query, fbUserId);

		Integer userId = null;
		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryString);

			while (rowSet.next()) {
				userId = rowSet.getInt("id");
			}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryString);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryString);
		}
		
		return userId;
	}	
	
	public Session getSessionUsingSessionId(String sessionId) {

		String query = "SELECT * from session where id = '%s'";
		final String queryString = String.format(query, sessionId);

		Session session = null;
		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryString);

			while (rowSet.next()) {
					session = new Session();
					session.setSessionId(rowSet.getString("id"));
					session.setUserId(rowSet.getInt("user_id"));
			}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryString);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryString);
		}
		
		return session;
	}	
	
	/*
	 * To update ads in database, will do a Update if ID is provided in the Payload, otherwise will create a new entry. 
	 */
	public User insertOrUpdateUser(User user) {

		String queryUser = "SELECT id from user where fb_user_id = '%s'";
		final String queryUserString = String.format(queryUser, user.getFbUserId());

		Integer id = null;
		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryUserString);

			while (rowSet.next()) {
					id = rowSet.getInt("id");
			}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryUserString);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryUserString);
		}
		
		if (null == id) {
			String query = "INSERT INTO user (fb_user_name, fb_first_name, fb_last_name, fb_access_token, city, country, created_time, updated_time, fb_user_id) "
					+ "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', NOW(), NOW(), '%s')";
			
	
			final String queryString = String.format(query, user.getUserName(),
					user.getFirstName(), user.getLastName(), user.getAcessToken(),
					user.getCity(), user.getCountry(), user.getFbUserId());
	
			System.out.println(queryString);
			
			KeyHolder keyHolder = new GeneratedKeyHolder();
			this.jdbcTemplate.update(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(
						Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(queryString,
							Statement.RETURN_GENERATED_KEYS);
					return ps;
				}
	
			}, keyHolder);
	
			user.setId(keyHolder.getKey().intValue());
		} else {
			String query = "UPDATE user set fb_user_name='%s', fb_first_name='%s', "
					+ "fb_last_name='%s', fb_access_token='%s', city='%s', country='%s'"
					+ ", updated_time=NOW() where fb_user_id='%s' ";
			
	
			final String queryString = String.format(query, user.getUserName(),
					user.getFirstName(), user.getLastName(), user.getAcessToken(),
					user.getCity(), user.getCountry(), user.getFbUserId());
	
			System.out.println(queryString);
			
			KeyHolder keyHolder = new GeneratedKeyHolder();
			this.jdbcTemplate.update(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(
						Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(queryString,
							Statement.RETURN_GENERATED_KEYS);
					return ps;
				}
	
			}, keyHolder);
			user.setId(id);
		}

		return user;
	}
	
	public WishList createOrUpdateWishList(WishList wishList) {
		
		if (null == wishList.getId() || wishList.getId() == 0) {
			wishList.getCategory().setCreator(wishList.getUser().getId());
			Category category = addCategory(wishList.getCategory());
			String query = "INSERT IGNORE INTO user_wishlist (user_id, category_id, created_time, additional_comment, status, update_time) "
					+ "VALUES ('%s', '%s', NOW(), '%s', '%s', NOW())";

			final String queryString = String.format(query, wishList.getUser().getId(),
					category.getId(), wishList.getAdditionalComments(),
					wishList.getStatus());

			KeyHolder keyHolder = new GeneratedKeyHolder();
			this.jdbcTemplate.update(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(
						Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(
							queryString, Statement.RETURN_GENERATED_KEYS);
					return ps;
				}

			}, keyHolder);
			
			wishList.setId(keyHolder.getKey().intValue());
		} else {
			String additionalComments = null;
			if (null != wishList.getAdditionalComments() && !wishList.getAdditionalComments().equals("")) {
				additionalComments = "additional_comment='" + wishList.getAdditionalComments() + "'";
			}

			String status = null;
			if (null != wishList.getStatus() && wishList.getStatus() != 0) {
				status = "status=" + wishList.getStatus().toString();
			}		
			
			String updateQuery = null;
			if (null != status || null != additionalComments) {
				updateQuery = "UPDATE user_wishlist set update_time=NOW()" ;
				if (null != status) {
					updateQuery += "," + status;
				}
				
				if (null != additionalComments) {
						updateQuery += "," + additionalComments;
				}
				updateQuery += " where id=" + wishList.getId(); 
				this.jdbcTemplate.update(updateQuery);
			}
		}
		
		return wishList;
			
	}	
	
	public void createOrUpdateProduct (Product product) {

		// check if category already exists
		Integer productId = getCategory(product.getName(), null);
		if (null == productId) { 
			final String query = "INSERT INTO product (name, categoryId, specs, url, creator, created_time) VALUES ('%s', '%s', '%s', '%s', '%s', NOW())";

			final String queryString = String.format(query, product.getName(), product.getCategoryId(), product.getSpecs(), product.getUrl(), product.getCreator());

			KeyHolder keyHolder = new GeneratedKeyHolder();
			this.jdbcTemplate.update(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(
						Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(
							queryString, Statement.RETURN_GENERATED_KEYS);
					return ps;
				}

			}, keyHolder);

			productId = keyHolder.getKey().intValue();

		}
		
		String query = "INSERT INTO user_product (category_id, user_id, created_time) "
				+ "VALUES ('%s', '%s', NOW(), '%s')";

		final String queryString = String.format(query, product.getCategoryId(),
				product.getCreator());

		this.jdbcTemplate.update(queryString);
	}	
	
	public List<WishList> getWishList(Integer userId, Integer page, Integer limit) {
		
		String queryDashboard = "SELECT *,user_wishlist.id AS wish_id FROM user_wishlist join category on " +
				"(user_wishlist.category_id=category.id) " ;
		if (null != userId )  {
			queryDashboard =  queryDashboard + " WHERE user_wishlist.user_id = " + userId;
		}

		if (null == page && null != limit) {
			page = 0;
			queryDashboard =  queryDashboard +  " limit " + page + "," + limit;
		}
		
		List<WishList> wishlist = new ArrayList<WishList>();

		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryDashboard);

			while (rowSet.next()) {
					WishList wish = new WishList();
					Category category = new Category();
					User user = new User();
					user.setId(rowSet.getInt("user_id"));
					
					category.setId(rowSet.getInt("category_id"));
					category.setName(rowSet.getString("name"));
					category.setUrl(rowSet.getString("url"));
					category.setType(rowSet.getString("type"));
					
					wish.setAdditionalComments(rowSet.getString("additional_comment"));
					wish.setId(rowSet.getInt("wish_id"));
					wish.setStatus(rowSet.getInt("status"));

					wish.setCategory(category);
					wish.setUser(user);
					
					wishlist.add(wish);
				}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryDashboard);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryDashboard);
		}

		return wishlist;
	}	
	
	public Map<Integer, Integer> getOtherUsersforWishList(List<Integer> categoryIds) {

		if (null == categoryIds || categoryIds.size() == 0) {
			return null;
		}
		
		Map<Integer, Integer> wishListCount = new HashMap<Integer, Integer>();

		String categories = "";
		for (Integer categoryId:categoryIds) {
			categories += categoryId.toString() + ",";
		}
		
		categories = categories.substring(0, categories.length() -1);
		
		String query = "SELECT category_id, count(*) AS count FROM user_wishlist " +
				"WHERE category_id in (%s) group by category_id";
		query = String.format(query, categories);
		
		List<WishList> wishlist = new ArrayList<WishList>();

		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(query);

			while (rowSet.next()) {
				wishListCount.put(rowSet.getInt("category_id"), rowSet.getInt("count"));
				}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + query);
		} catch (Exception e) {
			log.error("error in getting operator list " + query);
		}		
		
		return wishListCount;
	}
	
	private Integer getCategory(String categoryName, String type) {
		
		if (null == categoryName || null == type) {
			return null;
		}
		
		String queryDashboard = "SELECT id FROM category WHERE LOWER(name) = '" + categoryName.trim().toLowerCase() + "' AND LOWER(type)='" + type.trim().toLowerCase() + "'";

		Integer categoryId = null;
		
		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryDashboard);

			while (rowSet.next()) {
					categoryId = rowSet.getInt("id");
				}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryDashboard);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryDashboard);
		}

		return categoryId;
	}
	
	private Category addCategory(Category category) {
		
		// check if category already exists
		Integer categoryId = getCategory(category.getName(), category.getType());
		if (null == categoryId) { 
			final String query = "INSERT INTO category (name, creator, created_time, type, url) VALUES ('%s', '%s', NOW(), '%s', '%s')";

			final String queryString = String.format(query, category.getName(), category.getCreator(), category.getType(), category.getUrl());

			KeyHolder keyHolder = new GeneratedKeyHolder();
			this.jdbcTemplate.update(new PreparedStatementCreator() {
				@Override
				public PreparedStatement createPreparedStatement(
						Connection connection) throws SQLException {
					PreparedStatement ps = connection.prepareStatement(
							queryString, Statement.RETURN_GENERATED_KEYS);
					return ps;
				}

			}, keyHolder);

			categoryId = keyHolder.getKey().intValue();
			category.setId(categoryId);
		}

		category.setId(categoryId);
		return category;
	}	
	
	
	private Integer getProduct(String productName) {
		
		String queryDashboard = "SELECT id FROM product WHERE LOWER(name) = '" + productName.trim().toLowerCase() + "'";

		Integer productId = null;
		
		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryDashboard);

			while (rowSet.next()) {
					rowSet.getString("id");
				}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryDashboard);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryDashboard);
		}

		return productId;
	}	
	
	public Category getCategoryById(Integer id) {
		
		String queryDashboard = "SELECT * FROM category WHERE id = '" + id + "'";

		Category category = null;
		
		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryDashboard);

			while (rowSet.next()) {
					category = new Category();
					category.setName(rowSet.getString("name"));
					category.setType(rowSet.getString("type"));	
					category.setUrl(rowSet.getString("url"));
					category.setCreator(rowSet.getInt("creator"));					
					category.setId(id);
				}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryDashboard);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryDashboard);
		}

		return category;
	}	
	
	public Map<String, List<Category>> getCategories() {
		
		Map<String, List<Category>> categories = new HashMap<String, List<Category>>();
		
		String queryCategories = "SELECT * FROM category ";

		Integer categoryId = null;
		
		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryCategories);

			while (rowSet.next()) {
					Integer id = rowSet.getInt("id");
					String name = rowSet.getString("name");
					Integer creatorId = rowSet.getInt("creator");
					String type = rowSet.getString("type");
					String url = rowSet.getString("url");
					
					Category category = new Category();
					category.setCreator(creatorId);
					category.setId(id);
					category.setName(name);
					category.setUrl(url);
					
					if (categories.containsKey(type)) {
						categories.get(type).add(category);
					} else {
						List<Category> listCategory = new ArrayList<Category>();
						listCategory.add(category);
						categories.put(type, listCategory);
					}
				}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryCategories);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryCategories);
		}

		return categories;
	}	
	
	public List<Product> getProductByCategory(Integer CategoryId) {
		
		List<Product> products = new ArrayList<Product>();
		
		String queryCategories = "SELECT * FROM product where categoryId =" + CategoryId;

		try {
			SqlRowSet rowSet = jdbcTemplate.queryForRowSet(queryCategories);

			while (rowSet.next()) {
					Integer id = rowSet.getInt("id");
					Integer categoryId = rowSet.getInt("categoryId");					
					String name = rowSet.getString("name");
					Integer creator = rowSet.getInt("creator");
					String specs = rowSet.getString("specs");
					String url = rowSet.getString("url");					
					
					Product product = new Product();
					product.setCreator(creator);
					product.setId(id);
					product.setCategoryId(categoryId);
					product.setName(name);
					product.setSpecs(specs);
					product.setUrl(url);
					product.setId(id);

					products.add(product);
			}
		} catch (StringIndexOutOfBoundsException e) {
			log.error("error in getting operator list " + queryCategories);
		} catch (Exception e) {
			log.error("error in getting operator list " + queryCategories);
		}

		return products;
	}		
}